package com.example.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.common.result.Result;
import com.example.entity.dto.Pojo;
import com.example.entity.dto.PojoDto;
import com.example.entity.dto.SfcteDto;
import com.example.entity.vo.*;
import com.example.entity.yifei.SFCTE;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Author: LiangPanDeng
 * @Date: 2023/12/15/15:11
 * @Description:    派工/报工Mapper
 */
@Mapper
public interface SFCTEMapper extends BaseMapper<SFCTE> {

    @Select("SELECT TOP 1 MD002,TA021,TA001,TA002,TA015,TA006,TA034,TA035,TA026,TA027,TE001,TE002,TE003,TE023,SFCTD.UDF01 FROM SFCTE LEFT JOIN MOCTA ON TA001 = TE006 AND TA002 = TE007 LEFT JOIN SFCTD ON TD001 = TE001 AND TD002 = TE002 LEFT JOIN CMSMD ON MD001 = TA021 WHERE TE001 = #{te001} AND TE002 = #{te002}")
    MocTaAndSfcTeVo queryMocTaAndSfcTe(String te001,String te002);

    @Select("SELECT UDF07,TE001,TE002,TE003,TE004,TE006,TE007,TE008,TE009,TE011,UDF51,UDF52,UDF53,(SELECT MW002 FROM CMSMW WHERE MW001 = TE009) AS name,(SELECT ISNULL(SUM(a.TE011+a.UDF51+a.UDF52+a.UDF53),0) FROM SFCTE a WHERE a.UDF09 = SFCTE.TE001 AND a.UDF10 = SFCTE.TE002 AND a.UDF11 = SFCTE.TE003 AND a.TE001 = 'D410' AND a.TE009 = SFCTE.TE009 ) AS ybNum FROM SFCTE WHERE TE001 = #{te001} AND TE002 = #{te002}")
    List<SfcTeListVo> querySfcTeListVo(String te001,String te002);

    SFCTE queryById(Pojo pojo);

    /**
     * 临时测试检验项目查询
     * @param pojo
     * @return
     */
    List<SFCTE> queryPageTest(Pojo pojo);

    void updateYourEntity(String code);

    void updateBg(String code,String val);

    void updateYourEntityS(String code);

    /**
     * 查询已派工数量
     * @param TE006
     * @param TE007
     * @return
     */
    @DS("demo")
    @Select("select ISNULL(SUM(TE011),0) AS num from SFCTE LEFT JOIN SFCTD ON TE001 = TD001 AND TE002 = TD002 where TE001 = 'D420' AND TE006 = #{TE006} and TE007 = #{TE007} and SFCTD.UDF02 = #{uuid}")
    Integer querySfCtePgNum(String TE006, String TE007, String uuid);

    /**
     * 查询所有未完成生产的工单
     * @return
     */
    @DS("demo")
    List<SfctaMoctaVo> querySfCtAMoCtaList(String TA001, String TA002, String TA006,String TA003);

    @DS("demo")
    List<MoctastaVo> queryMoCtaSfCtAListAll(String TA001, String TA002, String TA006,String TA011);

    List<SFCTAVo> querySfctaVo(String TA001, String TA002);

    @Select("SELECT ISNULL(SUM(TA010),NULL)  FROM SFCTA WHERE TA001=#{TA001} AND TA002=#{TA002} AND TA003 = #{TA003}")
    Long queryta010(String TA001,String TA002,String TA003);


    @Delete("DELETE FROM SFCTD WHERE TD001 = #{TD001} AND TD002 = #{TD002} ")
    Integer deletesfctd(String TD001,String TD002);


    @DS("demo")
    List<SfctaMoctaVo> querySfCtaIPag(PojoDto pojoDto);


    /**
     * 查询报工单
     * @return
     */
    @DS("demo")
    @Select("SELECT  * FROM SFCTE WHERE TE001 = 'D410' AND TE006 = #{odd} and TE007 = #{Single} and TE008 = #{Gx}")
    List<SFCTE> querySfCte(String odd,String Single,String Gx);


    /**
     * 查询工单
     * @return
     */
    @DS("demo")
    @Select("SELECT TA015 FROM MOCTA WHERE TA001 = #{odd} and TA002 = #{Single}")
    Long querymocta(String odd,String Single);

    /**
     * 查询派工单
     * @return
     */
    @DS("demo")
    @Select("SELECT  * FROM SFCTE WHERE TE001 = 'D420' AND TE006 = #{odd} and TE007 = #{Single} and TE008 = #{Gx}")
    List<SFCTE> querySfCteS(String odd,String Single,String Gx);


    /**
     * 根据派工共单单别 和 派工单单号 获取 派工详细信息列表
     * @param single        单别
     * @param odd           单号
     * @return
     */
    @DS("demo")
    @Select("select *, (SELECT MW002 FROM CMSMW WHERE MW001 = TE009) AS name from SFCTE where TE001 = #{single} and TE002 = #{odd} order by TE003  ")
    List<SFCTE> getListByTE001WithTE002(String single, String odd);


    @DS("demo")
    @Select(" WITH Results AS (select *, (SELECT MW002 FROM CMSMW WHERE MW001 = TE009) AS name,(select ISNULL(SUM(TE011),0) from SFCTE where UDF09 = TE001 and UDF10 = TE002  and UDF11 = TE003) AS num from SFCTE where TE001 = 'D420' AND TE004 = #{code})" +
            "SELECT * FROM Results WHERE TE011 > num order by TE003")
    List<SFCTE> selectListStaff(String code);

    /**
     * 查询工序报工总数量
     * @param TE006
     * @param TE007
     * @param TE008
     * @return
     */
    @DS("demo")
    @Select("select ISNULL(SUM(TE011),0) AS num from SFCTE where TE001 = 'D410' AND TE006 = #{TE006} and TE007 = #{TE007} and TE008 = #{TE008} ")
    Integer querySfCteNum(String TE006, String TE007, String TE008);

    /**
     * 查询工序报工总数量和不良数量
     * @param TE006
     * @param TE007
     * @param TE008
     * @return
     */
    @DS("demo")
    @Select("select ISNULL(SUM(TE011),0) AS num,ISNULL(SUM(UDF51),0) AS blnum from SFCTE where TE001 = 'D410' AND TE006 = #{TE006} and TE007 = #{TE007} and TE008 = #{TE008} ")
    SfcteVo querySfCteNums(String TE006, String TE007, String TE008);

    @DS("demo")
    @Select("select * from SFCTE where TE001 = 'D420' AND TE006 = #{TE006} and TE007 = #{TE007} and TE008 = #{TE008}  order by TE002")
    List<SFCTE> gette001(String TE006, String TE007, String TE008);


    /**
     * 根据 单别生成年位为4为的单号
     * @param TE001 单别
     * @return
     */
    @DS("demo")
    @Select("SELECT SUBSTRING(CONVERT(CHAR(8),GETDATE(),112),1,8)+ ISNULL(right('000'+rtrim(cast(cast(MAX(SUBSTRING(TE002,9,3)) as numeric(16))+1 as char(3))),3),'001')\n" +
            "    from SFCTE WHERE TE002 LIKE SUBSTRING(CONVERT(CHAR(8),GETDATE(),112),1,6)+'%'  AND TE001=#{TE001}")
    String getNewTE002Year4(String TE001);

    /**
     * 根据 单别生成年位为4为的单号
     * @param TD001 单别
     * @return
     */
    @DS("demo")
    @Select("SELECT SUBSTRING(CONVERT(CHAR(8),GETDATE(),112),1,8)+ ISNULL(right('000'+rtrim(cast(cast(MAX(SUBSTRING(TD002,9,3)) as numeric(16))+1 as char(3))),3),'001')\n" +
            "    from SFCTD WHERE TD002 LIKE SUBSTRING(CONVERT(CHAR(8),GETDATE(),112),1,6)+'%'  AND TD001=#{TD001}")
    String getNewTD002Year4(String TD001);

    /**
     * 保存报工单
     * @param saveSFCTE
     */
    @DS("demo")
    @Insert("insert into SFCTE " +
            "(COMPANY, CREATOR, USR_GROUP, CREATE_DATE, MODIFIER, MODI_DATE, FLAG, " +
            "TE001, TE002, TE003, TE004, TE005, TE006, TE007, TE008, TE009, TE010, " +
            "TE011, TE012, TE013, TE014, TE015, TE016, TE017, TE018, TE019, TE020, " +
            "TE021, TE022, TE023, TE024, TE025, TE026, TE027, TE028, TE029, TE030, " +
            "TE031, TE032, UDF01, UDF02, UDF03, UDF04, UDF05, UDF06, UDF51, UDF52, " +
            "UDF53, UDF54, UDF55, UDF56, UDF07, UDF08, UDF09, UDF10, UDF11, UDF12, " +
            "UDF57, UDF58, UDF59, UDF60, UDF61, UDF62) " +
            "values (#{COMPANY},#{CREATOR},#{USR_GROUP},#{CREATE_DATE},#{MODIFIER},#{MODI_DATE},#{FLAG}," +
            "#{TE001},#{TE002},#{TE003},#{TE004},#{TE005},#{TE006},#{TE007},#{TE008},#{TE009},#{TE010}," +
            "#{TE011},#{TE012},#{TE013},#{TE014},#{TE015},#{TE016},#{TE017},#{TE018},#{TE019},#{TE020}," +
            "#{TE021},#{TE022},#{TE023},#{TE024},#{TE025},#{TE026},#{TE027},#{TE028},#{TE029},#{TE030}," +
            "#{TE031},#{TE032},#{UDF01},#{UDF02},#{UDF03},#{UDF04},#{UDF05},#{UDF06},#{UDF51},#{UDF52}," +
            "#{UDF53},#{UDF54},#{UDF55},#{UDF56},#{UDF07},#{UDF08},#{UDF09},#{UDF10},#{UDF11},#{UDF12}," +
            "#{UDF57},#{UDF58},#{UDF59},#{UDF60},#{UDF61},#{UDF62})")
    void save(SFCTE saveSFCTE);

    /**
     * 保存派工单
     * @param saveSFCTE
     */
    @DS("demo")
    @Insert("insert into SFCTE " +
            "(COMPANY, CREATOR, USR_GROUP, CREATE_DATE, MODIFIER, MODI_DATE, FLAG, " +
            "TE001, TE002, TE003, TE004, TE005, TE006, TE007, TE008, TE009, TE010, " +
            "TE011, TE012, TE013, TE014, TE015, TE016, TE017, TE018, TE019, TE020, " +
            "TE021, TE022, TE023, TE024, TE025, TE026, TE027, TE028, TE029, TE030, " +
            "TE031, TE032, UDF01, UDF02, UDF03, UDF04, UDF05, UDF06, UDF51, UDF52, " +
            "UDF53, UDF54, UDF55, UDF56, UDF07, UDF08, UDF09, UDF10, UDF11, UDF12, " +
            "UDF57, UDF58, UDF59, UDF60, UDF61, UDF62) " +
            "values (#{COMPANY},#{CREATOR},#{USR_GROUP},#{CREATE_DATE},#{MODIFIER},#{MODI_DATE},#{FLAG}," +
            "#{TE001},#{TE002},#{TE003},#{TE004},#{TE005},#{TE006},#{TE007},#{TE008},#{TE009},#{TE010}," +
            "#{TE011},#{TE012},#{TE013},#{TE014},#{TE015},#{TE016},#{TE017},#{TE018},#{TE019},#{TE020}," +
            "#{TE021},#{TE022},#{TE023},#{TE024},#{TE025},#{TE026},#{TE027},#{TE028},#{TE029},#{TE030}," +
            "#{TE031},#{TE032},#{UDF01},#{UDF02},#{UDF03},#{UDF04},#{UDF05},#{UDF06},#{UDF51},#{UDF52}," +
            "#{UDF53},#{UDF54},#{UDF55},#{UDF56},#{UDF07},#{UDF08},#{UDF09},#{UDF10},#{UDF11},#{UDF12}," +
            "#{UDF57},#{UDF58},#{UDF59},#{UDF60},#{UDF61},#{UDF62})")
    Integer saveSfcTeDto(SfcteDto saveSFCTE);

    /**
     * 根据 单别 单号 和序号 查询派工单详细信息
     * @param TE001
     * @param TE002
     * @param TE003
     * @return
     */
    @DS("demo")
    @Select("select * from SFCTE where TE001 = #{TE001} and TE002 = #{TE002} and TE003 = #{TE003}")
    SFCTE getByTE001WithTE002WithTE003(String TE001, String TE002, String TE003);

    @DS("demo")
    @Select("select ISNULL(SUM(TE011),0) from SFCTE where UDF09 = #{TE001} and UDF10 = #{TE002} and UDF11 = #{TE003}")
    BigDecimal getTE011WithTE001WithTE006WithTE007WithTE008(String TE001, String TE002, String TE003);

    @DS("demo")
    @Select("select ISNULL(SUM(TE011),0) from SFCTE where UDF09 = #{TE001} and UDF10 = #{TE002}")
    BigDecimal getSfcTe(String TE001, String TE002);

    @DS("demo")
    Result<List<MocTaAndSfcTeVo>> queryMocta();

    @DS("demo")
    List<SFCTE> qualityFrom(String page, String pageSize, String udf06, String start_time, String end_time);

    @DS("demo")
    List<SfcteAllVo> getUdf01();

    @DS("demo")
    List<SfcTeListVo> getlist(String udf01);

    @DS("demo")
    List<SfcTeListVo> getSfcte(String db,String dh);

    @DS("demo")
    BigDecimal getAlreadyBG(String te006, String te007, String te008);

    // 获取派工单的 所有报工合格数量
    @DS("demo")
    BigDecimal getBGhgNums(String te006, String te007, String te008);

    @DS("demo")
    BigDecimal getAlreadyBGmocty(@Param("uuid") String uuid, @Param("processNo") String processNo);

    @DS("demo")
    List<DeviceProgressVo> selectByMachineId(String machineId);

    // 新增的批量查询方法
    List<DeviceProgressVo> selectByMachineIds(@Param("machineIds") List<String> machineIds);

    List<DeviceProgressVo> getAll();
}
